﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraCharts;
using System.Data.SqlClient;
using Lists;
using GeneralLibs;

namespace BI
{
    public partial class UcDashBoard : UserControl
    {
        private static int _NumberOfReIndex = 0;

        public UcDashBoard()
        {
            InitializeComponent();
        }

        private void UcDashBoard_Load(object sender, EventArgs e)
        {
            if (DesignMode) return;
            DateTime now = GeneralLibs.CommonLib.GetSystemDateTime();
            dtNgayKetThuc.DateTime = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
            numInterval.Value = 7;
            cmbKyBaoCao.SelectedIndex = 17;
            cmbKyBaoCao.SelectedIndex = 18;
        }

        private void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DateTime ngayBatDau = new DateTime(); if (LocalSettings.HeThong.CheDoBiKiemTra) ngayBatDau = DateTime.Now.AddDays(-2); else ngayBatDau = dtNgayBatDau.DateTime; //DateTime ngayBatDau = dtNgayBatDau.DateTime;
                DateTime ngayKetThuc = dtNgayBatDau.DateTime;
                DateTime now = GeneralLibs.CommonLib.GetSystemDateTime();
                switch (cmbKyBaoCao.Text)
                {
                    case "Hôm nay":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tuần gần nhất":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0).AddDays(-7);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tháng này":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, System.DateTime.DaysInMonth(now.Year, now.Month), 23, 59, 59);
                        break;
                    case "Tùy chọn":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
                        break;
                    case "Cả năm":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    case "Quý I":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 3, 31, 23, 59, 59);
                        break;
                    case "Quý II":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 4, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 6, 30, 23, 59, 59);
                        break;
                    case "Quý III":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 7, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 9, 30, 23, 59, 59);
                        break;
                    case "Quý IV":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 10, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    default:
                        try
                        {
                            dtNgayBatDau.Enabled = false;
                            dtNgayKetThuc.Enabled = false;
                            string strThang = ((string)cmbKyBaoCao.SelectedItem).Substring(((string)(cmbKyBaoCao.SelectedItem)).LastIndexOf(" "));
                            int thang = int.Parse(strThang);

                            ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, thang, 1);
                            ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, thang,
                                            DateTime.DaysInMonth(GeneralLibs.Config.NamTaiChinh, thang), 23, 59, 59);
                        }
                        catch (SqlException sqlEx)
                        {
                            throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
                        }
                        catch (Exception ex)
                        {
                            if (ex is CustomException) throw ex;
                            else
                            {
                                throw new Exception(ex.Message);
                            }
                        }
                        break;
                }
                dtNgayBatDau.DateTime = ngayBatDau;
                dtNgayKetThuc.DateTime = ngayKetThuc;
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            //Nếu chưa đánh lại lần nào. Đã đánh rồi thì có thể k phải do lỗi này. K nên thực hiện nhiều lần
            if (Config.ThoiDiemReIndexGanNhat is NullableDate)
            {
                TimeSpan span = DateTime.Now - (DateTime)Config.ThoiDiemReIndexGanNhat.Date;
                if (span.TotalDays > 6) ReIndexDB();
            }
            else
            {
                ReIndexDB();
                Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
            }

            BasicForms.FrmProgressBarDev.StartShowProgress();
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = Config.TimeOut;
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                DateTime dtPointer = dtNgayBatDau.DateTime;
                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                //Guid taiKhoanTienMatID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("TienMat");
                //SoDuTaiKhoan soDuTaiKhoanTienMat = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanTienMatID);
                //ucDigitalNumberTienMat.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanTienMat.DuNo);

                //Guid taiKhoanTienGuiNganHangID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("TienGuiNganHang");
                //SoDuTaiKhoan soDuTaiKhoanTienGuiNganHang = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanTienGuiNganHangID);
                //ucDigitalNumberTienGuiNganHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanTienGuiNganHang.DuNo); 

                //Guid taiKhoanHangHoaID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("GiaMuaHangHoa");
                //SoDuTaiKhoan soDuTaiKhoanHangHoa = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanHangHoaID);
                //if (soDuTaiKhoanHangHoa.DuCo > 0)
                //    MessageBox.Show("Cần kiểm tra lại số dư tài khoản thuốc. Hiện tại đang âm " + soDuTaiKhoanHangHoa.DuCo.ToString());
                //ucDigitalNumberHangHoa.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanHangHoa.DuNo); 

                //Guid taiKhoanNoPhaiThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("PhaiThuKhachHang");
                //SoDuTaiKhoan soDuTaiKhoanNoPhaiThu = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanNoPhaiThuID);
                //ucDigitalNumberPhaiThu.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanNoPhaiThu.DuNo); 

                //Guid taiKhoanNoPhaiTraID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("PhaiTraNhaCungCap");
                //SoDuTaiKhoan soDuTaiKhoanNoPhaiTra = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanNoPhaiTraID);
                //ucDigitalNumberPhaiTra.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanNoPhaiTra.DuCo);

                ////ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));

                //decimal tongThu = TaiKhoan.TinhSoPhatSinhNo(cmd, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) +
                //                TaiKhoan.TinhSoPhatSinhNo(cmd, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                //                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienMatID, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                //                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienGuiNganHangID, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime);
                //decimal tongChi = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) +
                //                TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                //                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienMatID, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                //                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienGuiNganHangID, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime);

                //ucDigitalNumberTongThu.DisplayText = GeneralLibs.CommonLib.Decimal2Money(tongThu);
                //ucDigitalNumberTongChi.DisplayText = GeneralLibs.CommonLib.Decimal2Money(tongChi);

                #region tinh lai gop trong ky
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBan, SUM(Value7 - Value3) AS Lai\n" +
                                       "FROM KhoanMucChungTu \n" +
                                       "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                       "					WHERE GhiSo =1 AND ChungTuGocID IN (SELECT ChungTuID \n" +
                                       "											FROM ChungTu \n" +
                                       "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                       "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                       "																	) \n" +
                                       "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi\n" +
                                       "											)\n" +
                                       "                   )\n" +
                                       " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);

                ucDigitalNumberLaiGop.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["Lai"] != DBNull.Value && dr["DoanhSoBan"] != DBNull.Value)
                        {
                            //ucDigitalNumberLaiGop.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["Lai"]);
                            ucDigitalNumberLaiGop.DisplayText = GeneralLibs.CommonLib.Decimal2Money(CalculateLaiLoDetail(dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));
                            ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoBan"]);
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                //#region tinh doanh so mua hang
                //cmd.CommandText = "SELECT SUM(SotienNT) DoanhSoMuaHang\n" +
                //                    "FROM ChungTu  \n" +
                //                    "WHERE LoaiChungTuID = (SELECT ID FROM LoaiChungTu  \n" +
                //                    "						WHERE Ma = N'HoaDonMuaHang' \n" +
                //                    "						)         \n" +
                //                    "       AND SoTienNT IS NOT NULL AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi ";
                //cmd.Parameters.Clear();
                //cmd.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                //cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);
                //dr = cmd.ExecuteReader();

                //ucDigitalNumberMuaHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                //if (dr.Read())
                //{
                //    try
                //    {
                //        if (dr["DoanhSoMuaHang"] != DBNull.Value)
                //            ucDigitalNumberMuaHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoMuaHang"]);
                //    }
                //    catch
                //    {
                //    }
                //}
                //dr.Close();
                ////#endregion

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                ucTwoSeriesChart.Data.Rows.Clear();
                while (dtPointer <= dtNgayKetThuc.DateTime)
                {
                    DataRow row = ucTwoSeriesChart.Data.NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                    decimal phatSinh = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtPointer, dtPointer.AddDays(1));
                    row["Series1Value"] = phatSinh / 1000000;

                    row["Series2Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                    cmd.CommandText = "SELECT SUM(Value5 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                   AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi \n" +
                                        "											)\n" +
                                        "                   )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("ThoiDiemDau", dtPointer);
                    cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtPointer.AddDays(1));
                    dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        if (dr["Lai"] is decimal)
                            row["Series2Value"] = (decimal)dr["Lai"] / 1000000;
                        else
                        {
                            object abc = dr["Lai"];
                            string teyp = abc.GetType().ToString();
                            row["Series2Value"] = (decimal)0;
                        }
                    }
                    dr.Close();


                    ucTwoSeriesChart.Data.Rows.Add(row);
                    dtPointer = dtPointer.AddDays((int)numInterval.Value);
                }
                ucTwoSeriesChart.GenChart("Doanh số", "Lãi", ViewType.Bar, "Doanh số theo ngày (đv: triệu VNĐ)");
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
            finally
            {
                con.Close();
            }

            BasicForms.FrmProgressBarDev.FinishShowProgress();
        }

        protected decimal CalculateLaiLoDetail(DateTime thoiDiemDau, DateTime thoiDiemCuoi)
        {
            BasicForms.FrmProgressBarDev.StartShowProgress();
            decimal laiGop = 0;

            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            DsSource ds = new DsSource();
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = GeneralLibs.Config.TimeOut;
                SqlDataAdapter da = null;

                DataTable dtMuaHang = new DataTable();
                DataTable dtHangMuaTraLai = new DataTable();
                DataTable dtBanHang = new DataTable();
                DataTable dtHangBanTraLai = new DataTable();

                #region GetData MuaHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value3) ThanhTien \n" +
                                "FROM ChungTu B INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE B.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma ='PhieuNhapKho') \n" +
                                
                                "       AND @ThoiDiemDau <= B.Ngay AND B.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL --AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtMuaHang);
                #endregion
                #region GetData HangMuaTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangMuaTraLai') \n" +
                                
                                "       AND @ThoiDiemDau <= B.Ngay AND B.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL --AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangMuaTraLai);
                #endregion
                #region GetData BanHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, Sum(C.Value3) GiaTriXuat, SUM(C.Value8 * C.Value13)/100 TienChietKhau, SUM(C.Value5 - C.Value3) TienLai \n" +
                                "FROM ChungTu B INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE B.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma ='PhieuXuatKho') \n" +
                                
                                "       AND @ThoiDiemDau <= B.Ngay AND B.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL --AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtBanHang);
                #endregion
                #region GetData HangBanTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, SUM(C.Value5 - C.Value3) TienGiamLai \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangBanTraLai') \n" +
                                
                                "       AND @ThoiDiemDau <= B.Ngay AND B.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL --AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangBanTraLai);
                #endregion

                DataTable dtHangHoaCoGiaoDich = new DataTable();

                cmd.CommandText = "SELECT DISTINCT C.VatTuHangHoa1ID VatTuHangHoaID, D.Ten \n" +
                                "FROM ChungTu B INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "               INNER JOIN VatTuHangHoa D ON C.VatTuHangHoa1ID = D.ID \n" +
                                "WHERE  B.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma ='PhieuNhapkho' OR Ma = 'PhieuXuatKho') \n" +
                                
                                "       AND C.VatTuHangHoa1ID IS NOT NULL \n" +
                                "       AND @ThoiDiemDau <= B.Ngay AND B.Ngay <= @ThoiDiemCuoi \n" +
                                "ORDER BY D.Ten\n";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangHoaCoGiaoDich);


                for (int i = 0; i < dtHangHoaCoGiaoDich.Rows.Count; i++)
                {
                    VatTuHangHoa hangHoa = DanhMucVatTuHangHoa.Instance.GetByID((Guid)dtHangHoaCoGiaoDich.Rows[i]["VatTuHangHoaID"]);
                    if (hangHoa != null)
                    {
                        BasicForms.FrmProgressBarDev.Status = string.Format("Đang tính {0}/{1}", i, dtHangHoaCoGiaoDich.Rows.Count);

                        DataRow[] rowsBanHang = dtBanHang.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                        if (rowsBanHang.Length == 1)
                        {
                            if (rowsBanHang[0]["TienLai"] is decimal)
                            {
                                laiGop += (decimal)rowsBanHang[0]["TienLai"];
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
            finally
            {
                con.Close();
                con = null;
            }

            return laiGop;

            BasicForms.FrmProgressBarDev.FinishShowProgress();
        }

        private void ReIndexDB()
        {
            _NumberOfReIndex++;
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = Config.TimeOut;

                cmd.CommandText = string.Format("DECLARE @MyTable VARCHAR(255) \n" +
                                                "DECLARE myCursor CURSOR FOR \n" +
                                                "   SELECT table_name \n" +
                                                "   FROM information_schema.tables \n" +
                                                "   WHERE table_type = 'base table' \n" +
                                                "OPEN myCursor \n" +
                                                "FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "WHILE @@FETCH_STATUS = 0 \n" +
                                                "BEGIN \n" +
                                                "   PRINT 'Reindexing Table:  ' + @MyTable \n" +
                                                "   DBCC DBREINDEX(@MyTable, '', 80) \n" +
                                                "   FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "END \n" +
                                                "CLOSE myCursor \n" +
                                                "DEALLOCATE myCursor \n" +
                                                "EXEC sp_updatestats \n"
                                                );
                BasicForms.FrmProgressBarDev.StartShowProgress("Đang đánh lại chỉ mục cho các bảng trong CSDL để tăng tốc độ truy vấn");
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Gặp sự cố khi đánh lại chỉ mục cho các bảng trong CSDL");
            }
            finally
            {
                BasicForms.FrmProgressBarDev.FinishShowProgress();
                if (con.State == ConnectionState.Open) con.Close();
            }
        }
    }
}
